package com.stm.bi.report;

import com.alibaba.fastjson.JSON;
import com.stm.bi.model.Title;
import com.stm.common.util.ExcelDataExporttUtil;
import com.stm.framework.core.context.UserHolder;
import com.stm.framework.core.util.ClassUtil;
import com.stm.framework.dbcp.DataSourceConfig;
import com.stm.framework.dbcp.JdbcService;
import com.stm.framework.dto.Response;
import com.stm.framework.enums.Status;
import com.stm.framework.excel.ExcelDataExporter;
import com.stm.framework.excel.model.Column;
import com.stm.framework.model.CustomizePage;
import com.stm.framework.model.LoginUser;
import com.stm.log.OperLogProvider;
import com.stm.log.enums.ActionType;
import com.stm.bi.model.DatatablePage;
import com.stm.bi.model.DatatableQuery;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletResponse;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.apache.commons.collections4.map.CaseInsensitiveMap;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.stream.Collectors;

/**
 * @Author chenyingjun
 * @Desc
 * @Date 2024/3/27 10:57
 */
public abstract class AbsDatatableQueryExe<Q extends DatatableQuery, R extends DatatablePage> {
    protected static final Logger log = LoggerFactory.getLogger(AbsDatatableQueryExe.class);
    @Resource
    protected OperLogProvider operLogProvider;
    @Resource
    private ExcelDataExporter excelDataExporter;
    @Resource
    private ThreadPoolExecutor threadPoolExecutor;
    @Resource
    protected JdbcService jdbcService;
    @Resource(name = DataSourceConfig.DEFAULT_DATASOURCE)
    private DataSource defaultDataSource;

    private final static String SELECT_DORIS_COLUMN_SQL = "select column_name,column_comment from information_schema.columns where table_schema=? and table_name=? and column_name not in('org_no','org_id')";

    protected DatatablePage queryPage(String querySql,Object[] args,Integer pageSize,Integer pageNum){
        CustomizePage<Map> customizePage = jdbcService.selectPage(querySql,args,pageSize,pageNum);
        DatatablePage datatablePage = DatatablePage.of(customizePage);
        try {
            List<Title> titles = getTitlesBySql(querySql);
            datatablePage.setTitles(titles);
        } catch (JSQLParserException e) {
            throw new RuntimeException(e);
        }
        return datatablePage;
    }



    private List<Title> getTitlesBySql(String querySql) throws JSQLParserException {
        Select statement = (Select) CCJSqlParserUtil.parse(querySql);
        PlainSelect plainSelect = (PlainSelect) statement.getSelectBody();
        Map<String, String> tableAliasMa = getTableAliasMap(plainSelect);
        Map<String,List<Map>> columnListMap = new HashMap<>();
        for(Map.Entry<String, String> entry : tableAliasMa.entrySet()){
            String tableName = entry.getKey();
            List<Map> columns = getColumnsByTable(tableName);
            columnListMap.put(entry.getValue(),columns);
        }
        List<Map> columns = new ArrayList<>();
        for (SelectItem selectItem : plainSelect.getSelectItems()) {
            Expression expression = selectItem.getExpression();
            if (expression instanceof AllTableColumns) {
                AllTableColumns allColumns = (AllTableColumns) expression;
                columns.addAll(columnListMap.get(allColumns.getTable().getName()));
            } else if (expression instanceof AllColumns) {
                for(Map.Entry<String, List<Map>> entry : columnListMap.entrySet()){
                    columns.addAll(entry.getValue());
                }
            } else {
                String fullColumnName = expression.toString();
                int idx = fullColumnName.indexOf(".");
                String origTableName = null;
                String columnName;
                if (idx > -1) {
                    origTableName = fullColumnName.substring(0, idx);
                    columnName = fullColumnName.substring(idx + 1);
                } else {
                    columnName = fullColumnName;
                }
                Alias alias = selectItem.getAlias();
                String aliasName = alias == null ? columnName : alias.getName();
                Map column = columnListMap.get(origTableName).stream().filter(x->x.get("column_name").equals(aliasName)).findFirst().orElse(null);
                if(column != null){
                    columns.add(column);
                }
            }
        }
        // 将列信息转换为Title对象列表
        List<Title> titles = columns.stream().map(x->{
            return new Title().setCode((String) x.get("column_name")).setName((String) x.get("column_comment"));
        }).collect(Collectors.toList());
        return titles;
    }

    private static Map<String, String> getTableAliasMap( PlainSelect plainSelect) {
        Map<String, String> tableAlias = new CaseInsensitiveMap<>();
        Table table = (Table) plainSelect.getFromItem();
        String alias = table.getAlias() == null ? null : table.getAlias().getName();
        tableAlias.put(table.getName(), alias);
        if(plainSelect.getJoins() != null){
            for (Join join : plainSelect.getJoins()) {
                FromItem joinItem = join.getRightItem();
                Table joinTable = (Table) joinItem;
                tableAlias.put(joinTable.getAlias().getName(),joinTable.getName());
            }
        }
        return tableAlias;
    }


    /**
     * 根据表名获取标题列表
     * 此方法用于从指定的表中提取所有列名，并将其作为标题返回
     * 它首先获取数据源的schema，然后根据schema和表名查询表的列信息，
     * 最后将每列的列名封装成Title对象，收集到列表中返回
     *
     * @param tableName 表名，用于指定需要获取标题的表
     * @return 返回一个Title对象的列表，每个Title对象包含列的代码和名称
     */
    private List<Map> getColumnsByTable(String tableName){
        // 获取数据源的schema
        String schema = (String) ClassUtil.getFieldValueByName(defaultDataSource,"schema");
        // 查询表的列信息
        List<Map> columns = jdbcService.selectList(SELECT_DORIS_COLUMN_SQL,new Object[]{schema,tableName});

        // 返回Title对象列表
        return columns;
    }

    /**
     * 查询报表数据
     *
     * @param query 查询条件
     * @return 包含查询结果的响应对象
     */
    public Response<R> query(Q query){
        Long startTime = System.currentTimeMillis();
        LoginUser loginUser = UserHolder.getLoginUser();
        // 执行处理商品推移相关的报表查询，包括款式结构、售罄率、退货等
        R queryResult = query(loginUser.getOrgId(),loginUser.getOrgNo(),query);
        Response response = Response.of(queryResult);
        // 记录操作日志+耗时
        operLogProvider.create(ActionType.QUERY,"查询报表["+getReportName()+"]",query,null,response,Status.NORMAL);
        log.info("查询报表["+getReportName()+"]耗时："+(System.currentTimeMillis()-startTime)+"ms");
        return response;
    }

    /**
     * 导出报表数据
     *
     * @param servletResponse HTTP响应对象，用于输出导出的文件
     * @param query 查询条件
     * @return 包含导出结果的响应对象
     * @throws IOException 如果导出过程中发生I/O错误
     */
    public void export(HttpServletResponse servletResponse,Q query) throws IOException {
        Long startTime = System.currentTimeMillis();
        LoginUser loginUser = UserHolder.getLoginUser();
        R queryResult = query(loginUser.getOrgId(),loginUser.getOrgNo(),query);
        List<Title> titles = queryResult.getTitles();
        List<Column> columns = titles.stream().map(title -> {
            Column column = new Column().setTitle(title.getName()).setFieldName(title.getCode());
            return column;
        }).collect(Collectors.toList());
        String title = getReportName();
        if(syncExport(null)){
            threadPoolExecutor.execute(()->{
                ExcelDataExporttUtil.syncExportData(columns, queryResult.getDatas(),title, loginUser);
            });
            operLogProvider.create(ActionType.QUERY,"异步导出报表["+getReportName()+"]",query,null,Response.success(),Status.NORMAL);
            log.info("导出报表["+getReportName()+"]");
            Response response = Response.success("报表异步导出，稍后留意消息通知！");
            servletResponse.setContentType("application/json;charset=utf-8");
            servletResponse.getWriter().print(JSON.toJSONString(response));
        }else{
            excelDataExporter.export(servletResponse, columns, queryResult.getDatas(), title);
            operLogProvider.create(ActionType.QUERY,"导出报表["+getReportName()+"]",query,null,Response.success(),Status.NORMAL);
            log.info("导出报表["+getReportName()+"]耗时："+(System.currentTimeMillis()-startTime)+"ms");
        }

        //return response;
    }

    /**
     * 执行查询操作的抽象方法
     *
     * @param orgId 组织ID
     * @param orgNo 组织编号
     * @param query 查询条件
     * @return 查询结果
     */
    public abstract R query(Long orgId,String orgNo,Q query);

    /**
     * 获取报表名称的抽象方法
     *
     * @return 报表名称
     */
    public abstract String getReportName();

    /**
     * 判断是否同步导出报表的抽象方法
     *
     * @return 如果同步导出返回true，否则返回false
     */
    public abstract boolean syncExport(DatatablePage page);

}
